from flask import Flask, request, jsonify
from flask_cors import CORS
import tornado.wsgi
import tornado.httpserver
from tornado.ioloop import IOLoop
import logging
import re
import os

import configparser
import sys
from cryptography.fernet import Fernet
from collections import OrderedDict

from SqlServerHelper import SqlServerHelper
from datetime import datetime, timedelta
app = Flask(__name__)
# 跨域处理
CORS(app, supports_credentials=True)


# pyinstaller -F binCopare.py SqlServerHelper.py

# 配置日志记录
logging.basicConfig(filename='app.log', level=logging.ERROR, format='%(asctime)s - %(levelname)s - %(message)s')


def log_exception(e):
    """记录异常到日志文件"""
    logging.exception('Unhandled Exception: %s', str(e))

# 解析数据库配置文件
key = 'lzbVgGHnKZaGWs9wBnV5HM_VFSoKzKNTyH4J5_zZAeI='

def encrypt(value, key):
    f = Fernet(key)
    encrypted_value = f.encrypt(value.encode('utf-8'))
    return encrypted_value.decode('utf-8')

def decrypt(encrypted_value, key):
    f = Fernet(key)
    decrypted_value = f.decrypt(encrypted_value.encode('utf-8'))
    return decrypted_value.decode('utf-8')


# 创建配置文件解析器
config = configparser.ConfigParser()
#\ufeff 是 UTF-8 文件的字节顺序标记 (BOM)
# 使用 encoding='utf-8-sig' 来处理潜在的 BOM

# 读取配置文件
os.chdir(os.path.dirname(os.path.abspath(sys.argv[0])))
config.read('configs.ini', encoding='utf-8-sig')



driver = config['database']['driver']
server = config['database']['server']
database = config['database']['database']
database_pcw = config['database']['database_pcw']
# 解密用户名和密码
encrypted_username = config['database']['username']
encrypted_password = config['database']['password']
username = decrypt(encrypted_username, key)
password = decrypt(encrypted_password, key)
# 封装完整的数据库连接地址
connection_string = f"DRIVER={driver};SERVER={server};DATABASE={database};UID={username};PWD={password}"

# 新增数据库连接 增加PCW单复检 数据从写码软件数据库中获取 Code_Data
connection_string_pcw = f"DRIVER={driver};SERVER={server};DATABASE={database_pcw};UID={username};PWD={password}"

def hex_to_string(hex_string):
    try:
        decoded_string = bytes.fromhex(hex_string).decode("utf-8")
    except UnicodeDecodeError:
        decoded_string = ""
        for char in bytes.fromhex(hex_string):
            if char < 32 or char > 126:  # 控制字符或非 ASCII 字符
                decoded_string += "."
            else:
                decoded_string += chr(char)
    return decoded_string

# 对bin信息进行对比
def single_compare_hex_strings(compare_string):
    # 去掉空格并按照两位数为一组进行比较
    compare_string = compare_string.replace(" ", "")

    res = ""

    for i in range(0, len(compare_string), 2):
        # char1 = standard_string[i:i+2]
        char = compare_string[i:i+2]

        # if char1 != char2:
        #     differences.append(i // 2)
        #     compared_string += "<span style='color: red;font-weight: bold;'>{}</span> ".format(char2)
        # else:
        res += char + " "

        if (i + 2) % 32 == 0:
            res += "<br>"

    return res.strip()

# 对bin信息进行对比
# def compare_hex_strings(standard_string, compare_string):
#     # 去掉空格并按照两位数为一组进行比较
#     standard_string = standard_string.replace(" ", "")
#     compare_string = compare_string.replace(" ", "")
#
#     differences = []
#     compared_string = ""
#
#     for i in range(0, len(standard_string), 2):
#         char1 = standard_string[i:i+2]
#         char2 = compare_string[i:i+2]
#
#         if char1 != char2:
#             differences.append(i // 2)
#             compared_string += "<span style='color: red;font-weight: bold;'>{}</span> ".format(char2)
#         else:
#             compared_string += char2 + " "
#
#         if (i + 2) % 32 == 0:
#             compared_string += "<br>"
#
#     return compared_string.strip(), differences
# 判断字符串的后一半全是由0或者F组成的，不进行对比
def is_all_zero_or_f(s):
    half_length = len(s) // 2
    second_half = s[half_length:]
    return all(c in ("0", "F") for c in second_half)

# 处理特殊情况。当指定码位为FF或者00或者全是一样的时，不参与比较def validate_string(s, start_idx, end_idx):
def validate_string(s, start_idx, end_idx):
    # 先根据给定的范围截取字符串
    sub_str = s[start_idx-1:end_idx]
    # 检查是否整个字符串是相同的字符
    if all(c == sub_str[0] for c in sub_str):
        return True
    # 检查长度是否是偶数，以保证可以每两个字符一组
    if len(sub_str) % 2 != 0:
        return False
    # 遍历截取的字符串，每次步进2
    for i in range(0, len(sub_str), 2):
        # 检查每两个字符的组合
        if sub_str[i:i+2] not in ['FF', '00']:
            return False
    return True


def get_middle_part(text, delimiter='<br>', num_parts=3):
    """
    将字符串按指定的分隔符分为多个部分，并返回中间的那部分文本。

    :param text: 待分割的字符串
    :param delimiter: 分隔符，默认为'<br>'
    :param num_parts: 分割的部分数量，默认为3
    :return: 中间部分的文本
    """
    # 将字符串按照指定分隔符分割成列表
    lines = text.split(delimiter)
    
    # 计算每部分的长度
    total_lines = len(lines)
    part_size = total_lines // num_parts  # 每部分的行数

    # 切分为多个部分
    parts = [lines[i*part_size:(i+1)*part_size] for i in range(num_parts-1)]
    parts.append(lines[(num_parts-1)*part_size:])  # 最后一部分

    # 获取中间的部分
    middle_part = parts[num_parts // 2]

    # 将中间部分重新拼接为字符串
    middle_part_text = delimiter.join(middle_part)

    return middle_part_text

# 示例
# print(validate_string('FFAA00FF00', 1, 10))  # False
# print(validate_string('FFFF', 1, 4))  # True
# print(validate_string('AAAB', 1, 4))  # False

def compare_hex_strings(standard_string, compare_string, specialCodePathDirName, standard_string_parse, compare_string_parse):
    # 去掉空格并按照两位数为一组进行比较
    standard_string = standard_string.replace(" ", "")
    compare_string = compare_string.replace(" ", "")

    differences = []
    compared_string = ""

    # "Byte0为03，Byte2为00/21/22/23时，BIN比较只核对前面256位 （若为H3-DAC、HU-AOC则无法涵盖"）
    start_with_03_and_Byte2_is_00_21_22_23_07 = standard_string.startswith("03") and standard_string[4:6] in ['00', '21',
                                                                                                         '22', '23', '07']
    # Check if standard_string starts with "03", "0B", "18", or "19"
    start_with_03_or_0B = standard_string.startswith("03") or standard_string.startswith("0B")
    start_with_18_or_19_or_1E = standard_string.startswith("18") or standard_string.startswith("19") or standard_string.startswith("1E")
    # Byte0为0D、11时，如果box读取第1-128位和第129-256重复时，则只比较1-128位，反之则全部比对
    start_with_0D_or_11 = standard_string.startswith("0D") or standard_string.startswith("11")
    strat_with_06 = standard_string.startswith("06")

    start_with_03_and_Byte2_is_80 = standard_string.startswith("03") and standard_string[4:6] in ['80']
    # 线缆的只分为两种情况：
    # 1、当型号名Q28-XX或者QSFP-XX时，并且不是CO兼容和H3C兼容时，比较前128
    # 2、其它情况都只比较前256

    # < br > ....F2033817600 - < br > 1  240102... + < br >
    #  根据规律写出  正则表达式模式
    pattern = "-<br>[0-9]   2"

    # 使用re.search()进行匹配
    isCable = re.search(pattern, standard_string_parse)
    # 如果找到了匹配，说明是线缆，否则不是线缆
    # 线缆

    # 需求又有变化，有些特殊的情况，是不区分线缆和非线缆的，，现在把这些特殊的统一当成非线缆去处理
    # 详情见飞书文档 https://mah2eds8ab.feishu.cn/sheets/KOB8sTPVfhpom4tgLcccRBwfncf?sheet=Fa12OE  线缆比较规则

    if isCable is not None and not start_with_0D_or_11 and not start_with_03_and_Byte2_is_00_21_22_23_07 and not start_with_18_or_19_or_1E and not start_with_03_and_Byte2_is_80:
        for (index, i) in enumerate(range(0, len(standard_string), 2)):

            char1 = standard_string[i:i + 2]
            char2 = compare_string[i:i + 2]

            # 1、当型号名Q28 - XX或者QSFP - XX时，并且不是CO兼容和H3C兼容时，比较前128
            if ('Q28-' in standard_string_parse or 'QSFP-' in standard_string_parse) and 'CO' not in standard_string_parse and 'H3C' not in standard_string_parse:
                if (index >= 0 and index <= 127):
                    if char1 != char2:
                        differences.append(i // 2)
                        compared_string += "<span style='color: red;font-weight: bold;'>{}</span> ".format(char2)
                    else:
                        compared_string += char2 + " "
                else:
                    compared_string += char2 + " "
            # 2、其它情况都只比较前256
            else:
                # 存在特殊情况
                # 当码位出现数值FINISAR CORP时，需比对A0低位和A2高位, A0高位无需比对 （前128位和后128位）
                # 存在变化，当除了前128位必须比较，后面的码位如果由FF或者00组成时，也不比较，或者全是一样的时也不比较
                if 'FINISAR CORP' in standard_string_parse:
                    # 因为是每两个字符为一组，所以validate_string截取值从1开始的，传入的start end参数应该是正常索引值 X 2
                    # print(validate_string('030407100000000000000006670000000804000046494E4953415220434F52502E2020200000906546544C5838353734443342434C202020412020200352002E001A000043323430363037363535302D32202020323430363237202068F005F100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000FFFFFFFFFFFFFFFF', 1, 256))  # False 0-128
                    # print(validate_string('030407100000000000000006670000000804000046494E4953415220434F52502E2020200000906546544C5838353734443342434C202020412020200352002E001A000043323430363037363535302D32202020323430363237202068F005F100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000FFFFFFFFFFFFFFFF', 257,512)) # False 129-256
                    # print(validate_string('030407100000000000000006670000000804000046494E4953415220434F52502E2020200000906546544C5838353734443342434C202020412020200352002E001A000043323430363037363535302D32202020323430363237202068F005F100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000FFFFFFFFFFFFFFFF', 513,768)) # True 257-384
                    
                    
                    # Byte0为03时，当左侧最后128位中出现“E1
                    # E1”字样时，只比对前256位，后128位不比对
                    # [-256:]代表后128位
                    if 'E1' in compare_string[-256:]:
                        if (index >= 0 and index <= 127):
                            if char1 != char2:
                                differences.append(i // 2)
                                compared_string += "<span style='color: red;font-weight: bold;'>{}</span> ".format(
                                    char2)
                            else:
                                compared_string += char2 + " "
                        else:
                            compared_string += char2 + " "
                    else:
                    
                      if (index >= 0 and index <= 127) or (index >= 128 and index <= 255 and not validate_string(compare_string,257,512) and len(compare_string) >= 512) or (index >= 256 and index <= 383 and not validate_string(compare_string,513,768) and len(compare_string) >= 768):
                          if char1 != char2:
                              differences.append(i // 2)
                              compared_string += "<span style='color: red;font-weight: bold;'>{}</span> ".format(
                                  char2)
                          else:
                              compared_string += char2 + " "
                      else:
                          compared_string += char2 + " "
                else:
                    if (index >= 0 and index <= 255):
                        if char1 != char2:
                            differences.append(i // 2)
                            compared_string += "<span style='color: red;font-weight: bold;'>{}</span> ".format(
                                char2)
                        else:
                            compared_string += char2 + " "
                    else:
                        compared_string += char2 + " "

            if (i + 2) % 32 == 0:
                compared_string += "<br>"

    # 非线缆
    else:

        for (index,i) in enumerate(range(0, len(standard_string), 2)):

            char1 = standard_string[i:i+2]
            char2 = compare_string[i:i+2]

            # print('i',index,i,char1)
            # i 0 0 18
            # i 1 2 33
            # i 2 4 66
            # i 3 6 66
            # i 4 8 34

            # Byte0为0D、11时，如果box读取第1-128位和第129-256重复时，则只比较1-128位，反之则全部比对
            # 如果后128位全部是00或者FF的时候只比较前128位
            if start_with_0D_or_11:
                #  定义一个正则表达式，匹配前一半和后一半完全相等的字符串,字符串一定为双数
                pattern = r"^(\w+)\1$"
                match1 = re.search(pattern, compare_string)
                # 多加一个判断，如果共享文件里面的标准码也存在前128位和后128位相同时，只比较前128位
                match2 = re.search(pattern, standard_string)
                # 右侧基本信息包含Mellanox字符串时
                # 只复检比对前128位信息，后128位无需比对
                isHavingMellanox = 'Mellanox' in standard_string_parse

                # 右侧基本信息包含QSFPDD-2Q56PC015字符串时
                # 只复检比对前128位信息，后128位无需比对
                isHavingQSFPDD2Q56PC015 = 'QSFPDD-2<br>Q56PC015' in standard_string_parse

                # 200G QSFP 封装的线缆只写128位，A2高位不做对比
                # 当右侧后128位出现“127FMA627L3415”字样时，只比对前128位
                is127FMA627L3415 = '127FMA627L3415' in standard_string_parse

                # 新增对比条件
                # 当compare_string专为字符串之后，存在 2016 HPED字符串时，只比较前128，

                # 存在，只比较前128
                if match1 or match2 or (is_all_zero_or_f(compare_string) and is_all_zero_or_f(
                        standard_string)) or isHavingMellanox or '2016 HPED' in hex_to_string(compare_string) or is127FMA627L3415 or isHavingQSFPDD2Q56PC015:
                    if (index >= 0 and index <= 127):
                        if char1 != char2:
                            differences.append(i // 2)
                            compared_string += "<span style='color: red;font-weight: bold;'>{}</span> ".format(char2)
                        else:
                            compared_string += char2 + " "
                    else:
                        compared_string += char2 + " "
                else:  # 全部比较

                    if char1 != char2:
                        # 新增规则
                        # 当compare_string后128位全是FF
                        # standard_string中Byte191位是C1, Byte223为E1时，Byte191位和Byte223不比较
                        if is_all_zero_or_f(compare_string) and (
                                (i == 382 and char1 == 'C1') or (i == 446 and char1 == 'E1')):
                            compared_string += char2 + " "
                        else:
                            differences.append(i // 2)
                            compared_string += "<span style='color: red;font-weight: bold;'>{}</span> ".format(char2)
                    else:
                        compared_string += char2 + " "
            # 按照索引来简单一些
            elif start_with_03_and_Byte2_is_00_21_22_23_07:

                # specialCodePathDirName 判断当前的兼容类型H3、HU，则比较前128位和后128位，否则还是比较 0-255
                if 'H3' in specialCodePathDirName or 'HU' in specialCodePathDirName:
                    # 前128位 (index >= 0 and index <= 127)
                    # 后128位 i > len(standard_string) - 256     并且后128位不能为F/0 standard_string[-256:] 取的是后128位
                    if (index >= 0 and index <= 127) or (i >= len(standard_string) - 256 and len(
                            standard_string[-256:].replace('F', '').replace('0', '')) > 0) and len(standard_string) == len(compare_string):
                        if char1 != char2:
                            differences.append(i // 2)
                            compared_string += "<span style='color: red;font-weight: bold;'>{}</span> ".format(char2)
                        else:
                            compared_string += char2 + " "
                    else:
                        compared_string += char2 + " "
                else:
                    # 只比较前256位
                    if (index >= 0 and index <= 255):
                        if char1 != char2:
                            differences.append(i // 2)
                            compared_string += "<span style='color: red;font-weight: bold;'>{}</span> ".format(char2)
                        else:
                            compared_string += char2 + " "
                    else:
                        compared_string += char2 + " "
            # 低速率(03、0B)封装产品排除后8位，不进行比对。只比对A0低位0~127/A0高位128~255/A2高位128~247
            elif start_with_03_or_0B:
                # 前一半和后一半的数据完全一致
                pattern = r"^(\w+)\1$"
                match = re.search(pattern, compare_string)
          
                # 当左侧A0高位出现 "HP ProCurve Proprietary"时，只比对前128位与后128位，
                if standard_string.startswith("03") and len(standard_string) == 768 and'HP ProCurve' in get_middle_part(compare_string_parse, num_parts=3) and 'HP ProCurve' in get_middle_part(standard_string_parse, num_parts=3):
                  if (index >= 0 and index <= 127) or  (index >= 256 and index <= 383):
                    if char1 != char2:
                        differences.append(i // 2)
                        compared_string += "<span style='color: red;font-weight: bold;'>{}</span> ".format(char2)
                    else:
                        compared_string += char2 + " "
                  else:
                    compared_string += char2 + " "
                # 03开头，总长度256，前一半和后一半的内容一致，只比较前128位 长度需要x2
                elif compare_string.startswith("03") and len (compare_string) == 512 and match:

                  if index >= 0 and index <= 127 :
                    if char1 != char2:
                        differences.append(i // 2)
                        compared_string += "<span style='color: red;font-weight: bold;'>{}</span> ".format(char2)
                    else:
                        compared_string += char2 + " "
                  else:
                    compared_string += char2 + " "
                # 当出现CMUIARACAA10-3227-01V01 字符时只比较前256位，后128不比较
                elif 'CMUIARACAA10-322' in compare_string_parse:
                  if index >= 0 and index <= 255 :
                    if char1 != char2:
                        differences.append(i // 2)
                        compared_string += "<span style='color: red;font-weight: bold;'>{}</span> ".format(char2)
                    else:
                        compared_string += char2 + " "
                  else:
                    compared_string += char2 + " "
                # "GPON产品写码组合为128，后256位无需写入"
                # 当右侧出现“ 00056 ”字样时，复检只比对前128位，后256位无需比对
                elif '00056' in standard_string_parse:
                  if index >= 0 and index <= 127 :
                    if char1 != char2:
                        differences.append(i // 2)
                        compared_string += "<span style='color: red;font-weight: bold;'>{}</span> ".format(char2)
                    else:
                        compared_string += char2 + " "
                  else:
                    compared_string += char2 + " "
                else:
                  # If "03" or "0B" is at the beginning of standard_string, skip the last 16 characters (8 bytes)
                  if i < len(standard_string) - 16:
                      if char1 != char2:
                          differences.append(i // 2)
                          compared_string += "<span style='color: red;font-weight: bold;'>{}</span> ".format(char2)
                      else:
                          compared_string += char2 + " "
                  else:
                      compared_string += char2 + " "
            # QDD（18、19、E1）
              # "封装为：（18.19.1E) 时，lower page00：byte 0-1 ，byte 85-117，
              # upper page00H：byte 128-255
              # upper page01H： byte 259-260，byte 266-269、byte 328-329，byte 351-378
              # upper page03H：byte 512-639
              # 只比对以上位置"
            elif start_with_18_or_19_or_1E:
                # If "18" or "19" is at the beginning of standard_string, compare only specified positions
                if index in (0, 1) or (index >= 85 and index <= 117) or (index >= 128 and index <= 255) or (index >= 259 and index <= 260) or (index >= 266 and index <= 269) or (index >= 328 and index <= 329) or (index >= 351 and index <= 378) or (index >= 512 and index <= 639):
                    if char1 != char2:
                        differences.append(i // 2)
                        compared_string += "<span style='color: red;font-weight: bold;'>{}</span> ".format(char2)
                    else:
                        compared_string += char2 + " "
                else:
                    compared_string += char2 + " "
            
            elif strat_with_06:
                #当右侧码位出现“WMOTCUXAAA10-1989-03V03“字样时，复检只比对前128位，后128位无需对比
                if 'WMOTCUXAAA10-1989-03V03' in standard_string_parse:
                  if index >= 0 and index <= 127:
                    if char1 != char2:
                        differences.append(i // 2)
                        compared_string += "<span style='color: red;font-weight: bold;'>{}</span> ".format(char2)
                    else:
                        compared_string += char2 + " "
                  else:
                    compared_string += char2 + " "
                else:
                   if char1 != char2:
                      differences.append(i // 2)
                      compared_string += "<span style='color: red;font-weight: bold;'>{}</span> ".format(char2)
                   else:
                      compared_string += char2 + " "
            else:
                # Compare all characters for other cases
                if char1 != char2:
                    differences.append(i // 2)
                    compared_string += "<span style='color: red;font-weight: bold;'>{}</span> ".format(char2)
                else:
                    compared_string += char2 + " "

            if (i + 2) % 32 == 0:
                compared_string += "<br>"

    return compared_string.strip(), differences




# 对bin文件进行字符串解析
def binToParse(hex_string):
    hex_chunks = [hex_string[i:i+32] for i in range(0, len(hex_string), 32)]
    result_strings = []
    for chunk in hex_chunks:
        byte_chunk = bytes.fromhex(chunk)
        printable_chars = [chr(byte) if 32 <= byte < 127 else '.' for byte in byte_chunk]
        result_strings.append(''.join(printable_chars))
    parsed_string = '<br>'.join(result_strings)
    return parsed_string





# ts_recheck_res
@app.route('/pyapi/getRecheckResByConditions', methods=['POST'])
def getRecheckResByConditions():
    db_tool = SqlServerHelper(connection_string)
    # 查询条件示例
    conditions = {
        'orderNumber': request.json.get('orderNumber'),
        'binSerialNumber': request.json.get('binSerialNumber'),
        'checkRes': request.json.get('checkRes'),
        'deviceVersion': request.json.get('deviceVersion'),
    }
    res = db_tool.execute_select_with_conditions('ts_recheck_res', conditions, page=request.json.get('pageindex'), per_page=request.json.get('pagesize'))
    return {
     'res': res
    }


@app.route('/pyapi/getRecheckAllDetailPreByConditions', methods=['POST'])
def getRecheckAllDetailPreByConditions():
    db_tool = SqlServerHelper(connection_string)
    # 查询条件示例
    conditions = {
        'orderNumber': request.json.get('orderNumber'),
        'checkRes': request.json.get('checkRes'),
        'recheckStartTime': request.json.get('recheckStartTime'),
        'recheckEndTime': request.json.get('recheckEndTime'),
    }
    res = db_tool.execute_select_with_conditions('ts_recheck_all_detail_pre', conditions, page=request.json.get('pageindex'), per_page=request.json.get('pagesize'))
    return {
     'res': res
    }



#  模糊搜索接口
@app.route('/pyapi/getTsRecheckResByOrderNumberAndSnAndIsfinal', methods=['GET'])
def getTsResBySn():
    orderNumber = request.args.get('orderNumber')
    binSerialNumber = request.args.get('binSerialNumber')
    recheckStartTime = request.args.get('recheckStartTime')
    recheckEndTime = request.args.get('recheckEndTime')

    db_tool = SqlServerHelper(connection_string)
    # keyword = "CG2306214461-CDDE-100P1"  # 替换为实际的关键词
    # 只是判断是否存在，所以传 page = 1 看第一页是否有数据即可，没有使用select count(*) 是因为开发时间比较急，没时间写，只能先这样，后面有时间再优化
    page = 1
    per_page = 10
    query = f"SELECT * FROM ts_recheck_res WHERE orderNumber = '{orderNumber}' and binSerialNumber = '{binSerialNumber}' and isFinal = 1 and recheckTime >= '{recheckStartTime}' and recheckTime <= '{recheckEndTime}'"
    res = db_tool.execute_select(query, page=page, per_page=per_page)

    return {
     'res': res
    }



# CG2306214461-CDDE-100P
# 用户中途断网或者刷新页面，需要查询数据库保持原状态
@app.route('/pyapi/getTempByOrderNumber', methods=['GET'])
def getTempByOrderNumber():
    orderNumber = request.args.get('orderNumber')
    recheckEmployees = request.args.get('recheckEmployees')
    db_tool = SqlServerHelper(connection_string)


    # keyword = "CG2306214461-CDDE-100P1"  # 替换为实际的关键词
    page = 1
    per_page = 10
    query = f"SELECT * FROM ts_recheck_temp_res WHERE orderNumber = '{orderNumber}' AND recheckEmployees = '{recheckEmployees}'"
    res = db_tool.execute_select_without_pagination(query)

    return {
     'res': res
    }


# 保存erp传来的数据，保留证据
@app.route('/pyapi/saveErpRes', methods=['post'])
def save_erp_res():

    data = OrderedDict(sorted(request.json.items()))

    try:
        columns = []
        values = []
        db_tool = SqlServerHelper(connection_string)
        for key, value in data.items():
            # if value:
            columns.append(key)
            values.append(value)

        if columns:
            # 插入操作
            table_name = "ts_recheck_erp_res"
            insert_result = db_tool.execute_insert(table_name, columns, values)
            response = insert_result
        else:
            print("No non-empty attributes to insert.")
    except Exception as e:
        log_exception(e)
        response = {'status': 'error', 'message': '发生错误，请稍后重试。'}

    return jsonify(response)


# 将复检信息存入复检总表
@app.route('/pyapi/save_recheck_info_all', methods=['post'])
def save_recheck_info_all():

    data = OrderedDict(sorted(request.json.items()))
    current_date_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    data.update({"recheckTime": current_date_time})


    try:
        columns = []
        values = []
        db_tool = SqlServerHelper(connection_string)
        for key, value in data.items():
            # if value:
            columns.append(key)
            values.append(value)

        if columns:
            # 插入操作
            table_name = "ts_recheck_res"
            insert_result = db_tool.execute_insert(table_name, columns, values)

            insert_result.update({
                'recheckTime': current_date_time
            })
            response = insert_result
        else:
            print("No non-empty attributes to insert.")
    except Exception as e:
        log_exception(e)
        response = {'status': 'error', 'message': '发生错误，请稍后重试。'}

    return jsonify(response)


# 复检暂存表，当复检结束时会进行清空
@app.route('/pyapi/save_ts_recheck_temp_res', methods=['post'])
def save_ts_recheck_temp_res():
    data = OrderedDict(sorted(request.json.items()))

    try:
        columns = []
        values = []
        db_tool = SqlServerHelper(connection_string)
        for key, value in data.items():
            # if value:
            columns.append(key)
            values.append(value)

        if columns:
            # 插入操作
            table_name = "ts_recheck_temp_res"
            insert_result = db_tool.execute_insert(table_name, columns, values)
            response = insert_result
        else:
            print("No non-empty attributes to insert.")
    except Exception as e:
        log_exception(e)
        response = {'status': 'error', 'message': '发生错误，请稍后重试。'}

    return jsonify(response)

# 通过订单号删除临时数据
@app.route('/pyapi/deleteTempResByOrderNumber', methods=['get'])
def deleteTempResByOrderNumber():
    orderNumber = request.args.get('orderNumber')
    recheckEmployees = request.args.get('recheckEmployees')
    try:
        db_tool = SqlServerHelper(connection_string)
        deleteRes = db_tool.execute_delete("ts_recheck_temp_res", {
            'orderNumber': orderNumber,
            'recheckEmployees': recheckEmployees
        })
        response = deleteRes
    except Exception as e:
        log_exception(e)
        response = {'status': 'error', 'message': '发生错误，请稍后重试。'}

    return jsonify(response)

@app.route('/pyapi/save_ts_recheck_all_pre', methods=['post'])
def ts_recheck_all_detail():

    data = OrderedDict(sorted(request.json.items()))

    try:
        columns = []
        values = []
        db_tool = SqlServerHelper(connection_string)
        for key, value in data.items():
            # if value:
            columns.append(key)
            values.append(value)

        if columns:
            # 插入操作
            table_name = "ts_recheck_all_detail_pre"
            insert_result = db_tool.execute_insert(table_name, columns, values)
            response = insert_result
        else:
            print("No non-empty attributes to insert.")
    except Exception as e:
        log_exception(e)
        response = {'status': 'error', 'message': '发生错误，请稍后重试。'}

    return jsonify(response)


@app.route('/pyapi/compare_hex_strings_api', methods=['POST'])
def compare():

    # 获取对比信息的数组

    # compare_data = request.json.get('compare_data')
    standard_string = request.json.get('standard_string_original')
    compare_string = request.json.get('compare_string_original')
    # bin文件最近的一层，用来判定当前的兼容
    specialCodePathDirName = request.json.get('specialCodePathDirName')

    # 对原16进制数据进行字符串翻译
    standard_string_parse = binToParse(standard_string.replace(" ", ""))
    compare_string_parse = binToParse(compare_string.replace(" ", ""))

    # 比较字符串并获取结果,右侧解析加上参数standard_string_parse判断是否包含特殊字符
    formatted_compare_string, differences = compare_hex_strings(standard_string, compare_string, specialCodePathDirName, standard_string_parse, compare_string_parse)

    # 去除 standard_string 中的所有空格
    formatted_standard_string = standard_string.replace(" ", "")

    # 格式化 standard_string 的展示格式
    formatted_standard_string = ' '.join(
        [formatted_standard_string[i:i + 2] for i in range(0, len(formatted_standard_string), 2)])
    formatted_standard_string = '<br>'.join(
        [formatted_standard_string[i:i + 48] for i in range(0, len(formatted_standard_string), 48)]).strip()

    result = {
        'standard_string': formatted_standard_string,
        'compare_string': formatted_compare_string,
        'standard_string_parse': standard_string_parse,
        'compare_string_parse': compare_string_parse,
        'differences': differences,
        'shareRes': bool(len(differences) == 0)
    }
    return jsonify({
        'result': result,
    })




@app.route('/pyapi/getErpTestData', methods=['GET'])
def getErpTestData():
    db_tool = SqlServerHelper(connection_string)


    query = f"SELECT * FROM ts_recheck_test_erp"
    res = db_tool.execute_select_without_pagination(query)
    return jsonify(res)

@app.route('/pyapi/getErpTestDataById', methods=['GET'])
def getErpTestDataById():
    id = request.args.get('id')
    db_tool = SqlServerHelper(connection_string)


    query = f"SELECT * FROM ts_recheck_test_erp  WHERE id = '{id}'"
    res = db_tool.execute_select_without_pagination(query)
    return jsonify(res)


@app.route('/pyapi/insertErpTestData', methods=['post'])
def insertErpTestData():

    data = OrderedDict(sorted(request.json.items()))

    try:
        columns = []
        values = []
        db_tool = SqlServerHelper(connection_string)
        for key, value in data.items():
            # if value:
            columns.append(key)
            values.append(value)

        if columns:
            # 插入操作
            table_name = "ts_recheck_test_erp"
            insert_result = db_tool.execute_insert(table_name, columns, values)
            response = insert_result
        else:
            print("No non-empty attributes to insert.")
    except Exception as e:
        log_exception(e)
        response = {'status': 'error', 'message': '发生错误，请稍后重试。'}

    return jsonify(response)

@app.route('/pyapi/updateErpTestData', methods=['POST'])
def updateErpTestData():

    set_values = request.json.get('set_values')
    conditions = request.json.get('conditions')
    # set_values = {'your_field': 'new_value'}
    # conditions = {'your_field': 'old_value'}
    db_tool = SqlServerHelper(connection_string)
    res = db_tool.execute_update('ts_recheck_test_erp', set_values, conditions)

    return {
     'res': res
    }
# 模拟
@app.route('/pyapi/erptest', methods=['POST'])
def erptest():
    orderNo = request.json.get('orderNo')
    serialNo = request.json.get('serialNo')

    db_tool = SqlServerHelper(connection_string)

    # query = f"SELECT * FROM ts_recheck_test_erp WHERE orderNo = '{orderNo}' and serialNo = '{serialNo}'"
    query = f"""
SELECT orderNo, '{serialNo}' as serialNo, modelName, manufacturerName, waveLength, speed,  transmissionDistance, checkTime
FROM ts_recheck_test_erp
WHERE orderNo = '{orderNo}'
  AND LEN('{serialNo}') = LEN(LEFT(serialNo, CHARINDEX('-', serialNo) - 1))
  AND '{serialNo}' BETWEEN 
      LEFT(serialNo, CHARINDEX('-', serialNo) - 1) 
      AND 
      RIGHT(serialNo, LEN(serialNo) - CHARINDEX('-', serialNo))
ORDER BY id ASC;
"""
    res = db_tool.execute_select_without_pagination(query)
    # print(res)


    try:
        if 'results' in res and len(res['results']) == 0:
            response = {
                "result": "fail",
                "code": 403,
                "msg": "未匹配到写码单信息!",
                "data": {}
            }
            # print(response)
            return response
        else:
            obj = res['results'][len(res['results'])-1]
            obj['checkTime'] = obj['checkTime'].strftime('%Y-%m-%d %H:%M:%S')
            response = {
                "result": "success",
                "code": 200,
                "msg": "成功",
                "data": obj
            }
            # print('模拟erp接口结果', response)
            return response
    except Exception as e:
        print('err', str(e))
        response = {
            "result": "error",
            "code": 500,
            "msg": "服务器内部错误",
            "data": {}
        }
        return response


@app.route('/pyapi/hello')
def hello():
    hex_string = "0304070000000280100105010d0050ff".replace(" ", "")  # 16进制字符串

    # 将16进制字符串转换为字节序列
    try:
        byte_sequence = bytes.fromhex(hex_string)

        # 将字节序列转换为字符串
        result_string = byte_sequence.decode('utf-8')
    except :
        result_string = 'error'


    return result_string


def list_files_in_folder(folder):
    local_file_paths = []
    for root, dirs, files in os.walk(folder):
        for file in files:
            file_path = os.path.join(root, file)
            local_file_paths.append(file_path)
    return local_file_paths

# 获取符合要求的全部路径
def list_all_files(root_folder):
    file_paths = set()  # Use a set for uniqueness

    for root, dirs, files in os.walk(root_folder):
        local_file_paths = list_files_in_folder(root)
        file_paths.update(local_file_paths)  # Add local paths to the set
    return list(file_paths)

# 存在 订单号后面加上数量的单， CG2210274450-CDNC-220P ，只能通过CG2210274450-CDNC去获取当前路径，缩小范围
def get_min_path(root_folder, substring):

    try:
        # 获取 root_folder 下的所有文件夹
        folders = [f for f in os.listdir(root_folder) if os.path.isdir(os.path.join(root_folder, f))]
        # 在文件夹中查找符合条件的文件
        for folder in folders:
            if folder.startswith(substring) or folder == substring:
                return os.path.join(root_folder, folder)
        return ''
    except Exception as e:
        print('get_min_path_err', str(e))
        print(root_folder, substring)
        return ''


def convert_date_format(input_date):
    try:
        date_obj = datetime.strptime(input_date, "%Y-%m-%d")
        year = date_obj.year
        month = date_obj.month
        day = date_obj.day

        formatted_date = f"{year}年\\{month}月\\{month}-{day:02}"
        # formatted_date = f"{year}/{month}/{day}"
        return formatted_date

    except ValueError:
        return None  # Return None for invalid input
# def convert_date_format(input_date):
#     try:
#         # 解析输入日期字符串
#         date_obj = datetime.datetime.strptime(input_date, "%Y-%m-%d")
#         year = date_obj.year
#         month = date_obj.month
#         day = date_obj.day
#
#         # 格式化日期为 "YYYY/MM/D" 格式
#         formatted_date = f"{year}\\{month}月\\{day}"
#         print(formatted_date)
#         return formatted_date
#     except ValueError:
#         return None  # 对于无效输入返回 None

# 通过 订单号+sn 确定唯一的bin文件路径 并取出共享文件夹里面的数据
@app.route('/pyapi/getSharePublicDataBin', methods=['POST'])
def getSharePublicDataBin():
    try:

        # orderNumber = request.json.get('orderNumber')
        # sn = request.json.get('sn')
        # # date = convert_date_format(request.json.get('date'))
        # date = request.json.get('date')
        # nas_path = request.json.get('nas_path')
        # print('订单号',orderNumber)
        # print('sn号',sn)
        # print('清点时间',request.json.get('date'))
        # print('主文件夹',nas_path)
        # nas_path = r'\\10.36.2.253\写码文件2\写码组\写码文件'  # NAS服务器的路径
        # nas_path = r'\\10.36.2.253\写码文件2\写码组\写码文件\2023年\1月\1-03\CG2210274450-CDNC-220P'  # NAS服务器的路径
        # orderNumber = 'G2240064655.bin'
        # 插入变量 日期和订单号
        # variable_path = os.path.join(date, 'te')
        # variable_path = '2023年\\10月\\10-11\\te'
        # 生成的最终文件夹
        # root_folder = os.path.join(nas_path, variable_path)
        # print('root_folder', root_folder)
        orderNumber = request.json.get('orderNumber')
        sn = request.json.get('sn')
        date = request.json.get('date')
        nas_path = request.json.get('nas_path')
        # 获取波长处理华为共享文件夹的问题
        wavelength = request.json.get('wavelength')
        variable_path = os.path.join(nas_path, date)
        # 通过订单号缩小最小路径范围
        full_path = get_min_path(variable_path, orderNumber)

        # 文件找不到时的提示路径
        hintPath = os.path.join(variable_path, orderNumber)
        # 如果没有找到包含orderNumber的文件下，直接结束程序
        if len(full_path) == 0:
            return {
                'sn': sn,
                'standard_string': '',
                'binPath': hintPath
            }


        # 找到的目标sn路径
        all_files = list_all_files(full_path)
        bininfo = [s.strip() for s in all_files if sn in s]
        if len(bininfo) > 0:
            #  "fat" if is_fat else "not fat"
            huWavelengthArr = [s.strip() for s in bininfo if '-'+wavelength in s] 
            with open(huWavelengthArr[0] if len(huWavelengthArr) > 0 else bininfo[0], 'rb') as f:
                data = f.read()
                hex_data = data.hex()


            return {
                'sn': sn,
                'standard_string': hex_data,
                'binPath': bininfo[0]
            }
        else:
            return {
                'sn': sn,
                'standard_string': '',
                'binPath': hintPath
            }
    except Exception as e:
        print('err', str(e))
        return {
            'sn': sn,
            'standard_string': '',
            'binPath': hintPath
        }


# 获取数据库中的配置详情 是否开启或者关闭某个基本属性的对比
@app.route('/tt', methods=['get'])
def tt():
    return jsonify({'11':'11'})

# 获取数据库中的配置详情 是否开启或者关闭某个基本属性的对比
@app.route('/pyapi/getTsRecheckAttrConfig', methods=['get'])
def getTsRecheckConfig():
    db_tool = SqlServerHelper(connection_string)
    try:
        query = f"SELECT * FROM ts_recheck_config"
        res = db_tool.execute_select_without_pagination(query)
        response = res
    except Exception as e:
        log_exception(e)
        response = {'status': 'error', 'message': '发生错误，请稍后重试。'}

    return jsonify(response)




@app.route('/pyapi/saveBoxParseExpection', methods=['post'])
def saveBoxParseExpection():

    data = OrderedDict(sorted(request.json.items()))

    try:
        columns = []
        values = []
        db_tool = SqlServerHelper(connection_string)
        for key, value in data.items():
            # if value:
            columns.append(key)
            values.append(value)

        if columns:
            # 插入操作
            table_name = "ts_rechcek_box_parse_exception"
            insert_result = db_tool.execute_insert(table_name, columns, values)
            response = insert_result
        else:
            print("No non-empty attributes to insert.")
    except Exception as e:
        log_exception(e)
        response = {'status': 'error', 'message': '发生错误，请稍后重试。'}

    return jsonify(response)



# 德国复检接口 ------------------------------------------------------------------------------------------------------------------------------------------------------------
@app.route('/pyapi/getRecheckAllDetailPreByConditionsDg', methods=['POST'])
def getRecheckAllDetailPreByConditionsDg():
    db_tool = SqlServerHelper(connection_string)
    # 查询条件示例
    conditions = {
        'orderNumber': request.json.get('orderNumber'),
        'checkRes': request.json.get('checkRes'),
        'recheckStartTime': request.json.get('recheckStartTime'),
        'recheckEndTime': request.json.get('recheckEndTime'),
    }
    res = db_tool.execute_select_with_conditions('ts_recheck_all_detail_pre_DG', conditions, page=request.json.get('pageindex'), per_page=request.json.get('pagesize'))
    return {
     'res': res
    }

#  模糊搜索接口
@app.route('/pyapi/getTsRecheckResByOrderNumberAndSnAndIsfinalDg', methods=['GET'])
def getTsRecheckResByOrderNumberAndSnAndIsfinalDg():
    orderNumber = request.args.get('orderNumber')
    binSerialNumber = request.args.get('binSerialNumber')
    recheckStartTime = request.args.get('recheckStartTime')
    # recheckEndTime = request.args.get('recheckEndTime')
    recheckEndTime = request.args.get('recheckEndTime')

    # todo
    # 统一将结束时间调大一秒，避免查询数据为空，暂时不知道怎么解决这个bug
    # 将字符串转换为 datetime 对象
    endTime_dt = datetime.strptime(recheckEndTime, "%Y-%m-%d %H:%M:%S")
    # 将结束时间增加一秒
    endTime_dt += timedelta(seconds=1)
    # 将调整后的结束时间转换为字符串
    recheckEndTime = endTime_dt.strftime("%Y-%m-%d %H:%M:%S")
    db_tool = SqlServerHelper(connection_string)

    page = 1
    per_page = 10
    query = f"SELECT * FROM ts_recheck_res_DG WHERE orderNumber = '{orderNumber}' and binSerialNumber = '{binSerialNumber}' and isFinal = 1 and recheckTime >= '{recheckStartTime}' and recheckTime <= '{recheckEndTime}'"
    res = db_tool.execute_select(query, page=page, per_page=per_page)

    return {
     'res': res
    }

# --------------------------------------------------------------------------------------------------------------



# 新加坡复检接口 ------------------------------------------------------------------------------------------------------------------------------------------------------------
@app.route('/pyapi/getRecheckAllDetailPreByConditionsSingapore', methods=['POST'])
def getRecheckAllDetailPreByConditionsSingapore ():
    driver = 'SQL Server'
    server = '10.36.230.125'
    database = 'Recheck'
    username = 'sa'
    password = 'king@123'
    db_tool = SqlServerHelper(f"DRIVER={driver};SERVER={server};DATABASE={database};UID={username};PWD={password}")
    # 查询条件示例
    conditions = {
        'orderNumber': request.json.get('orderNumber'),
        'checkRes': request.json.get('checkRes'),
        'recheckStartTime': request.json.get('recheckStartTime'),
        'recheckEndTime': request.json.get('recheckEndTime'),
    }
    res = db_tool.execute_select_with_conditions('ts_recheck_all_detail_pre', conditions, page=request.json.get('pageindex'), per_page=request.json.get('pagesize'))
    return {
     'res': res
    }

#  模糊搜索接口
@app.route('/pyapi/getTsRecheckResByOrderNumberAndSnAndIsfinalSingapore', methods=['GET'])
def getTsRecheckResByOrderNumberAndSnAndIsfinalSingapore():
    orderNumber = request.args.get('orderNumber')
    binSerialNumber = request.args.get('binSerialNumber')
    recheckStartTime = request.args.get('recheckStartTime')
    # recheckEndTime = request.args.get('recheckEndTime')
    recheckEndTime = request.args.get('recheckEndTime')

    # todo
    # 统一将结束时间调大一秒，避免查询数据为空，暂时不知道怎么解决这个bug
    # 将字符串转换为 datetime 对象
    endTime_dt = datetime.strptime(recheckEndTime, "%Y-%m-%d %H:%M:%S")
    # 将结束时间增加一秒
    endTime_dt += timedelta(seconds=1)
    # 将调整后的结束时间转换为字符串
    recheckEndTime = endTime_dt.strftime("%Y-%m-%d %H:%M:%S")

    # 新加坡服务器
    driver = 'SQL Server'
    server = '10.36.230.125'
    database = 'Recheck'
    username = 'sa'
    password = 'king@123'
    db_tool = SqlServerHelper(f"DRIVER={driver};SERVER={server};DATABASE={database};UID={username};PWD={password}")
    page = 1
    per_page = 10
    query = f"SELECT * FROM ts_recheck_res WHERE orderNumber = '{orderNumber}' and binSerialNumber = '{binSerialNumber}' and isFinal = 1 and recheckTime >= '{recheckStartTime}' and recheckTime <= '{recheckEndTime}'"
    res = db_tool.execute_select(query, page=page, per_page=per_page)

    return {
     'res': res
    }

# --------------------------------------------------------------------------------------------------------------


# 获取自动化复检设备信息订单号
@app.route('/pyapi/getAutoRecheckDeviceBySn', methods=['get'])
def getAutoRecheckDeviceBySn():
    sn = request.args.get('sn')
    db_tool = SqlServerHelper(connection_string)
    try:
        query = f"SELECT * FROM ts_auto_device_equipemt WHERE sn = '{sn}'"
        res = db_tool.execute_select_without_pagination(query)
        response = res
    except Exception as e:
        log_exception(e)
        response = {'status': 'error', 'message': '发生错误，请稍后重试。'}

    return jsonify(response)

@app.route('/pyapi/getAutoRecheckResByConditions', methods=['POST'])
def getAutoRecheckResByConditions():
    db_tool = SqlServerHelper(connection_string)
    # 查询条件示例
    conditions = {
        'orderNumber': request.json.get('orderNumber'),
        'binSerialNumber': request.json.get('binSerialNumber'),
        'shareRes': request.json.get('checkRes'),
        'isAuto': True
    }
    res = db_tool.execute_select_with_conditions('ts_recheck_res', conditions, page=request.json.get('pageindex'), per_page=request.json.get('pagesize'))
    return {
     'res': res
    }

# 复检SN排重 ，查询所有数据中，是否存在已经复检通过的SN
@app.route('/pyapi/findRecheckIsExist', methods=['POST'])
def findRecheckIsExist():
    db_tool = SqlServerHelper(connection_string)
    # 查询条件示例
    conditions = {
        'binSerialNumber': request.json.get('binSerialNumber'),
        'checkRes': True,
    }
    res = db_tool.does_record_exist('ts_recheck_res', conditions)
    return {
     'res': res
    }

# 编写码
def binToHex(hex_str):  # 将十六进制字符串转换为字节数组
    try:
        byte_data = bytes.fromhex(hex_str)  # 尝试将十六进制字符串转换为字节数组
    except ValueError:  # 如果十六进制格式错误，捕获异常
        print(f"Invalid hex string: {hex_str}")
        return ""  # 返回一个空字符串或默认值

    # 将字节数组解码成字符串，同时将不可打印的字符替换为 "."
    decoded_str = ''.join(chr(b) if 32 <= b <= 126 else '.' for b in byte_data)
    return decoded_str
@app.route('/pyapi/getBinByCodeDataBase', methods=['POST'])
def getBinByCodeDataBase():
    orderNumber = request.json.get('orderNumber')
    binSerialNumber = request.json.get('binSerialNumber')
    systemSerialNumber = request.json.get('systemSerialNumber')
    modelName = request.json.get('modelName')
    # binFile = request.json.get('binFile')
    # 写码数据库
    db_tool = SqlServerHelper(connection_string_pcw)
    page = 1
    per_page = 10
    # print(binSerialNumber)
    # print(systemSerialNumber)
    # 判断是否是单文件写码，如果是单文件写码则通过系统SN去数据库查找，否则通过编码SN去查找
    # 正则表达式匹配字母
    matches = re.findall(r'[a-zA-Z]', binSerialNumber)
    # 检查是否包含多个字母
    if len(matches) >= 2 and '-' not in binSerialNumber:
        query = f""" SELECT 
                  CASE 
                  WHEN LEFT(CONVERT(VARCHAR(MAX), a.BinFile, 1), 2) = '0x' 
                    THEN UPPER(SUBSTRING(CONVERT(VARCHAR(MAX), a.BinFile, 1), 3, LEN(CONVERT(VARCHAR(MAX), a.BinFile, 1)) - 2))
                    ELSE UPPER(CONVERT(VARCHAR(MAX), a.BinFile, 1))
                  END AS bin,
                  COALESCE(b.modelName, '') as modelName, 
                  COALESCE(b.manufacturerName, '') as manufacturerName, 
                  COALESCE(b.waveBand, '') as waveLength, 
                  COALESCE(b.speed, '') as speed, 
                  COALESCE(b.transmissionDistance, '') as transmissionDistance,
                  a.compatible
              FROM [Code_Data].[dbo].[BinaryFiles] a 
              LEFT JOIN [SwitchTestPro].[dbo].[ts_erp_data_test] b
                  ON a.orderNo = b.orderNo
                  AND '{systemSerialNumber}' >= b.serialNumber_Min 
                  AND '{systemSerialNumber}' <= b.serialNumber_Max 
                  AND b.serialNumber_Min IS NOT NULL
                  AND b.serialNumber_Max IS NOT NULL 
                  AND b.serialNumber_Min <> '' 
                  AND b.serialNumber_Max <> ''
              WHERE a.orderNo = '{orderNumber}'
                  AND '{systemSerialNumber}' = a.serialNumber AND a.Inactive = 0 
              ORDER BY a.BId DESC
          """
        # AND a.modelName = '{modelName}
    else:
        query = f""" SELECT 
                  CASE 
                  WHEN LEFT(CONVERT(VARCHAR(MAX), a.BinFile, 1), 2) = '0x' 
                    THEN UPPER(SUBSTRING(CONVERT(VARCHAR(MAX), a.BinFile, 1), 3, LEN(CONVERT(VARCHAR(MAX), a.BinFile, 1)) - 2))
                    ELSE UPPER(CONVERT(VARCHAR(MAX), a.BinFile, 1))
                  END AS bin,
                  COALESCE(b.modelName, '') as modelName, 
                  COALESCE(b.manufacturerName, '') as manufacturerName, 
                  COALESCE(b.waveBand, '') as waveLength, 
                  COALESCE(b.speed, '') as speed, 
                  COALESCE(b.transmissionDistance, '') as transmissionDistance,
                  a.compatible
              FROM [Code_Data].[dbo].[BinaryFiles] a 
              LEFT JOIN [SwitchTestPro].[dbo].[ts_erp_data_test] b
                  ON a.orderNo = b.orderNo
                  AND '{systemSerialNumber}' >= b.serialNumber_Min 
                  AND '{systemSerialNumber}' <= b.serialNumber_Max 
                  AND b.serialNumber_Min IS NOT NULL
                  AND b.serialNumber_Max IS NOT NULL 
                  AND b.serialNumber_Min <> '' 
                  AND b.serialNumber_Max <> ''
              WHERE a.orderNo = '{orderNumber}' 
                  AND '{binSerialNumber}' = a.SerialNo AND a.Inactive = 0
              ORDER BY a.BId DESC
          """
    # query = f""" SELECT 
    #               CASE 
    #               WHEN LEFT(CONVERT(VARCHAR(MAX), a.BinFile, 1), 2) = '0x' 
    #                 THEN UPPER(SUBSTRING(CONVERT(VARCHAR(MAX), a.BinFile, 1), 3, LEN(CONVERT(VARCHAR(MAX), a.BinFile, 1)) - 2))
    #                 ELSE UPPER(CONVERT(VARCHAR(MAX), a.BinFile, 1))
    #               END AS bin,
    #               COALESCE(b.modelName, '') as modelName, 
    #               COALESCE(b.manufacturerName, '') as manufacturerName, 
    #               COALESCE(b.waveBand, '') as waveLength, 
    #               COALESCE(b.speed, '') as speed, 
    #               COALESCE(b.transmissionDistance, '') as transmissionDistance,
    #               a.compatible
    #           FROM [Code_Data].[dbo].[BinaryFiles] a 
    #           LEFT JOIN [SwitchTestPro].[dbo].[ts_erp_data_test] b
    #               ON a.orderNo = b.orderNo
    #               AND '{systemSerialNumber}' >= b.serialNumber_Min 
    #               AND '{systemSerialNumber}' <= b.serialNumber_Max 
    #               AND b.serialNumber_Min IS NOT NULL
    #               AND b.serialNumber_Max IS NOT NULL 
    #               AND b.serialNumber_Min <> '' 
    #               AND b.serialNumber_Max <> ''
    #           WHERE a.orderNo = '{orderNumber}' 
    #               AND '{binSerialNumber}' = a.SerialNo AND a.Inactive = 0 AND a.Status = 1
    #           ORDER BY a.BId DESC
    #       """
        # AND CONVERT(VARCHAR(MAX), a.binFile, 1) = '0x{binFile}'
    # query = f""" SELECT 
    #               CASE 
    #               WHEN LEFT(CONVERT(VARCHAR(MAX), a.BinFile, 1), 2) = '0x' 
    #                 THEN UPPER(SUBSTRING(CONVERT(VARCHAR(MAX), a.BinFile, 1), 3, LEN(CONVERT(VARCHAR(MAX), a.BinFile, 1)) - 2))
    #                 ELSE UPPER(CONVERT(VARCHAR(MAX), a.BinFile, 1))
    #               END AS bin,
    #               COALESCE(b.modelName, '') as modelName, 
    #               COALESCE(b.manufacturerName, '') as manufacturerName, 
    #               COALESCE(b.waveBand, '') as waveLength, 
    #               COALESCE(b.speed, '') as speed, 
    #               COALESCE(b.transmissionDistance, '') as transmissionDistance,
    #               a.compatible
    #           FROM [Code_Data].[dbo].[BinaryFiles] a 
    #           LEFT JOIN [SwitchTestPro].[dbo].[ts_erp_data_test] b
    #               ON a.orderNo = b.orderNo
    #               AND '{binSerialNumber}' >= b.serialNumber_Min 
    #               AND '{binSerialNumber}' <= b.serialNumber_Max 
    #               AND b.serialNumber_Min IS NOT NULL
    #               AND b.serialNumber_Max IS NOT NULL 
    #               AND b.serialNumber_Min <> '' 
    #               AND b.serialNumber_Max <> ''
    #           WHERE a.orderNo = '{orderNumber}'
    #               AND '{binSerialNumber}' = a.SerialNumber AND a.Status = 1 AND a.modelName = '{modelName}'
    #           ORDER BY a.BId DESC
    #       """
    print(query)
    res = db_tool.execute_select(query, page=page, per_page=per_page)
    if res['code'] == "00" and len(res['results']) > 0:
        
        # 解析出查询的bin文件解析出型号名，进行筛选

        filtered_list = [item for item in res['results'] if modelName in binToHex(item['bin'])]

        # 打印筛选后的结果
        if len(filtered_list)> 0:
          # print(filtered_list[0])
          filtered_list[0]['serialNo'] = binSerialNumber
          response = {
              "result": "success",
              "code": 200,
              "msg": "成功",
              "data": filtered_list[0]
          }
          return response
        else:
          response = {
              "result": "fail",
              "code": 403,
              "msg": "未匹配到写码单信息!",
              "data": {}
          }
          return response
    else:
        response = {
            "result": "fail",
            "code": 403,
            "msg": "未匹配到写码单信息!",
            "data": {}
        }
        return response

# 新增PCW复检
@app.route('/pyapi/getPcwInfo', methods=['POST'])
def getPcwInfo():
    orderNumber = request.json.get('orderNo')
    binSerialNumber = request.json.get('serialNo')
    db_tool = SqlServerHelper(connection_string_pcw)
    page = 1
    per_page = 10
    #query = f"SELECT * FROM ts_recheck_res_DG WHERE orderNumber = '{orderNumber}' and binSerialNumber = '{binSerialNumber}' and isFinal = 1 and recheckTime >= '{recheckStartTime}' and recheckTime <= '{recheckEndTime}'"

    # query = f"""SELECT a.arrangeTime as checkTime, b.modelName, b.manufacturerName, b.waveBand as waveLength, b.speed, b.transmissionDistance
    #             FROM [Code_Data].[dbo].[SalesSchedule] a
    #             JOIN [SwitchTestPro].[dbo].[ts_erp_data_test]   b
    #             ON a.orderNo = '{orderNumber}' AND '{binSerialNumber}' >= a.serialNumber_Min AND '{binSerialNumber}' <= a.serialNumber_Max AND a.serialNumber_Min IS NOT NULL
    #             AND a.serialNumber_Max IS NOT NULL AND a.serialNumber_Min <> '' AND a.serialNumber_Max <> ''
    #             AND b.orderNo = '{orderNumber}' AND '{binSerialNumber}' >= b.serialNumber_Min AND '{binSerialNumber}' <= b.serialNumber_Max AND b.serialNumber_Min IS NOT NULL
    #             AND b.serialNumber_Max IS NOT NULL AND b.serialNumber_Min <> '' AND b.serialNumber_Max <> ''"""
    query = f"""SELECT FORMAT(a.arrangeTime, 'yyyy-MM-dd HH:mm:ss.fff') AS checkTime,  
                    COALESCE(b.modelName, '') as modelName, 
                    COALESCE(b.manufacturerName, '') as manufacturerName, 
                    COALESCE(b.waveBand, '') as waveLength, 
                    COALESCE(b.speed, '') as speed, 
                    COALESCE(b.transmissionDistance, '') as transmissionDistance
                FROM [Code_Data].[dbo].[SalesSchedule] a 
                LEFT JOIN [Code_Data].[dbo].[Api_Data_Orders_20230905] b
                    ON a.orderNo = b.orderNo
                    AND '{binSerialNumber}' >= b.serialNumber_Min 
                    AND '{binSerialNumber}' <= b.serialNumber_Max 
                    AND b.serialNumber_Min IS NOT NULL
                    AND b.serialNumber_Max IS NOT NULL 
                    AND b.serialNumber_Min <> '' 
                    AND b.serialNumber_Max <> ''
                WHERE a.orderNo = '{orderNumber}' 
                    AND '{binSerialNumber}' >= a.serialNumber_Min 
                    AND '{binSerialNumber}' <= a.serialNumber_Max 
                    AND a.serialNumber_Min IS NOT NULL
                    AND a.serialNumber_Max IS NOT NULL 
                    AND a.serialNumber_Min <> '' 
                    AND a.serialNumber_Max <> ''"""
    # print(query)
    res = db_tool.execute_select(query, page=page, per_page=per_page)
    if res['code'] == "00" and len(res['results']) > 0:
        res['results'][len(res['results']) - 1]['serialNo'] = binSerialNumber
        response = {
            "result": "success",
            "code": 200,
            "msg": "成功",
            "data": res['results'][len(res['results']) - 1]
        }
        return response
    else:
        response = {
            "result": "fail",
            "code": 403,
            "msg": "未匹配到写码单信息!",
            "data": {}
        }
        return response

    # if 'results' in res and len(res['results']) == 0:
    #     response = {
    #         "result": "fail",
    #         "code": 403,
    #         "msg": "未匹配到写码单信息!",
    #         "data": {}
    #     }
    #     print(response)
    #     return response
    # else:
    #     obj = res['results'][len(res['results']) - 1]
    #     obj['checkTime'] = obj['checkTime'].strftime('%Y-%m-%d %H:%M:%S')
    #     response = {
    #         "result": "success",
    #         "code": 200,
    #         "msg": "成功",
    #         "data": obj
    #     }
@app.route('/pyapi/getSystemInfoBySap', methods=['POST'])
def getSystemInfoBySap():
    orderNumber = request.json.get('orderNo')
    binSerialNumber = request.json.get('serialNo')
    db_tool = SqlServerHelper(connection_string_pcw)
    page = 1
    per_page = 10
      # ,[speed]  --速率
      # ,[compatible] -- 兼容
      # ,[supplierNo] -- 供应商
      # ,[serialNumber1] -- 系统序列号起始
      # ,[serialNumber2] -- 系统序列号截止
      # ,[modelName]     -- 定制型号名
      # ,[systemModelname] -- 系统型号名
      # ,[manufacturerName] -- 厂商名
      # ,[waveBand]         -- 模块波段
      # ,[transmissionDistance1] -- 模块距离
      # ,[transmissionDistance2] -- 线缆距离
    query = f"""  SELECT 
                  COALESCE(systemModelname, '') as modelName, 
                  COALESCE(manufacturerName, '') as manufacturerName, 
                  COALESCE(waveBand, '') as waveLength, 
                  COALESCE(speed, '') as speed, 
                  CASE 
                      WHEN CHARINDEX('-', '{binSerialNumber}') > 0 THEN COALESCE(transmissionDistance2, '')
                      ELSE COALESCE(transmissionDistance1, '')
                  END as transmissionDistance
              FROM [Code_Data].[dbo].[ProductOrders]
              WHERE a.orderNo = '{orderNumber}' 
                  AND '{binSerialNumber}' >= serialNumber1 
                  AND '{binSerialNumber}' <= serialNumber2 
                  AND serialNumber1 IS NOT NULL
                  AND serialNumber2 IS NOT NULL 
                  AND serialNumber1 <> '' 
                  AND serialNumber2 <> ''
              """
    # print(query)
    res = db_tool.execute_select(query, page=page, per_page=per_page)
    if res['code'] == "00" and len(res['results']) > 0:
        res['results'][len(res['results']) - 1]['serialNo'] = binSerialNumber
        response = {
            "result": "success",
            "code": 200,
            "msg": "成功",
            "data": res['results'][len(res['results']) - 1]
        }
        return response
    else:
        response = {
            "result": "fail",
            "code": 403,
            "msg": "未匹配到写码单信息!",
            "data": {}
        }
        return response


# 新增编写码一体化编码状态查询
@app.route('/pyapi/getCodeInfo', methods=['POST'])
def getCodeInfo():
    # orderNumber = request.json.get('orderNumber')
    # binSerialNumber = request.json.get('binSerialNumber')
    # modeName = request.json.get('modeName')
    db_tool = SqlServerHelper(connection_string_pcw)
        # 查询条件示例
    conditions = {
        'orderNo': request.json.get('orderNo'),
        'SerialNo': request.json.get('SerialNo'),
        'serialNumber': request.json.get('serialNumber'),
        'Inactive': 0 # 必须为0才有效
        # 'modelName': request.json.get('modelName'),
  
    }
    # print(conditions)
    res = db_tool.getCodeDataList('BinaryFiles', conditions, page=request.json.get('pageindex'), per_page=request.json.get('pagesize'))
    # print(res)
      # 遍历结果，转换BinFile字段
    for item in res['results']:
        if 'BinFile' in item:
            # 将二进制数据转换为base64编码的字符串
            item['BinFile'] = item['BinFile'].hex()
    # return {
    #  'res': res
    # }
    return jsonify(res)


# 创建 Tornado WSGI 应用
wsgi_app = tornado.wsgi.WSGIContainer(app)

# 从配置文件读取端口号
port = config['port']['port']

# 创建 Tornado HTTP 服务器并绑定到指定端口
http_server = tornado.httpserver.HTTPServer(wsgi_app)
http_server.listen(int(port), address="0.0.0.0")  # 修改为你想要的端口

print(f'端口号【{port}】服务运行中')
# 启动 Tornado 事件循环
IOLoop.instance().start()










